Skip to main content

RSMS results storage and retrieval — design

Status: Active — Azure Table Storage holds the plume concentration grid; a small set of JSON blobs remains under rsms-results. Parquet/DuckDB paths for RSMS scenario results remain historical only (archive).

Prototype / validation: Sudhir verified a table implementation with numpy.float32 payloads; see rsms-notebooks/river_plume_square_pulse_notebook_block_format/*_v2.ipynb. Rows are indexed by mile index along the river (fixed spacing) and time index (hourly steps over the simulated week).

Related: rsms-results-table-storage-rework-plan — worker + rollout context; rsms-master-tasklist — open backlog; SSOT artifacts ssot/rsms-ssot.json and ssot/rsms-domain-units-ssot.md. Historical Parquet/DuckDB: archive.

Where the pipeline runs (dev → prod)​

StageRole
Local/devUse run_rsms_cli.py, run_rsms_handlers, and unit/integration tests beside engine outputs — production uploads always flow through the queue-backed worker.
Target / prodrsms-worker-function is the canonical runtime: a self-hosted Python worker (typically queue_worker.py polling Azure Storage Queue, with rsms-api-fastapi enqueueing jobs) runs the RSMS engine and related executables (Neutshell, BLTM, CXPLT, etc.) in a controlled working directory, then invokes the same results-storage logic (rsms_results_storage: parse .PLT / .MAS, upsert PlumeByTime / PlumeByMile, upload minified edge_timeseries.json and mass_balance.json, optional engine-files). Local testing may use run_rsms_cli.py without the queue.

The storage design is worker-shaped: inputs are local paths immediately after the engine finishes; no blob download to feed aggregation into tables.

1. Goals and scope​

GoalNotes
SSOT for names, units, shapesssot/rsms-ssot.json — bump schemaVersion when table entities or blob JSON schemas change.
Table-first plume gridTwo logical table families (names may match Sudhir’s PlumeByTime / PlumeByMile): one row per time slice (concentration vs mile index) and one row per mile slice (concentration vs time index). Payloads are binary: float32 array, 4 bytes per element, little-endian (NumPy default).
Fixed river spacing0.2 mi (one-fifth mile) between sample points along the reach (product decision — supersedes earlier 0.1 mi / 0.25 mi notes). Physical distance from spill for index i is i * mile_step when the grid starts at 0 (exact zero-based mapping is locked in implementation + SSOT).
Bounded row sizeWith M mile indices and T time indices, PlumeByTime row payload size ≈ M × 4 bytes; PlumeByMile row ≈ T × 4 bytes. Example from prototype: M = 401, T = 216 → 1604 B and 864 B per row — well under Azure Table’s 64 KiB per-property practical limit. At ~200 mi span and 0.2 mi step, M ≈ 1000 → PlumeByTime row ≈ 4 KB; still small at prototype scale.
JSON blobs (minified)edge_timeseries.json, mass_balance.json under {riverbasin_id}/{scenario_id}/ — UTF-8, no significant whitespace in production ( json.dumps(..., separators=(',', ':')) or equivalent).
Scenario = resultsOne scenario_id (GUID); results keyed by scenario (and river basin) as today. No results_id.
No Parquet CXPLT gridDual cxplt_*.parquet files are not written for the product pipeline anymore.

Out of scope here: REST specifics beyond app/results/router.py (see /docs + rsms-master-tasklist.md for follow-ups); auth rules; pagination policy.

2. Azure Table Storage — plume concentration​

2.1 Semantics​

  • Mile index: Integer (or fixed zero-based ordinal) along the modeled river from the spill — not necessarily the legacy SQL Miles float; mapping index → miles_from_spill uses mile_step = 0.2 unless SSOT says otherwise.
  • Time index: Integer hour (or timestep ordinal) over the simulation horizon (e.g. one week hourly — T steps such as 216 in the prototype).
  • PlumeByTime: One table row per time index t. The float32 payload is length M: concentration at every mile index at that time. Full read = O(M) decode.
  • PlumeByMile: One table row per mile index m. The payload is length T: concentration at that mile across all times. Full read = O(T) decode.

Either slice supports the main chart access patterns; Sudhir measured ~0.13 s per single-row read (environment-dependent).

2.2 Partitioning and keys (TBD in implementation)​

Lock partition key, row key, and property names (ConcentrationBytes, etc.) in rsms-results-table-storage-rework-plan.md phase 1 when aligning with the v2 notebook exporter. Must support:

  • Point read by (scenario, time_index) for fixed-hour CXPLT-style charts.
  • Point read by (scenario, mile_index) for fixed-mile slices.
  • Optional metadata rows or Table Storage entities for MilesCount, TimesCount, mile_step, schema version.

2.3 Encoding​

  • dtype: numpy.float32 ( float32 ), 4 bytes per value.
  • Order: C-order flat array: for PlumeByTime, index m is position m in the buffer (document exact convention in SSOT).

2.4 API JSON rounding (read path)​

Decision: Keep Table payloads as float32 (storage size unchanged). In rsms-api-fastapi, after numpy.frombuffer decode, apply vectorized rounding so HTTP JSON does not show (a) float32 decimal tails when widened to JSON numbers, or (b) IEEE-754 noise from mile_index * mile_step (e.g. 0.6000000000000001 for a 0.2 mi grid).

OutputRounding
ConcentrationScenario field concentration_tolerance (Azure Scenarios entity, same as Sudhir edge / write pipeline). After float32 decode: np.round(x / tol) * tol, then np.round(..., n) with n = ceil(-log10(tol)) so JSON does not show binary tails (e.g. 0.9800000000000001). If missing or invalid, DEFAULT_CONCENTRATION_TOLERANCE from app/constants.py (0.001).
Mile axisScenario-agnostic 0.2 mi grid: np.round(i * mile_step, 1) (one decimal place).
Hour axisnp.round(i * hour_step, 0) for hour_step = 1
Domain endpointsSame decimal rules on scalar index * step values for miles/hours

Scope: Response serialization only — partition keys, row keys, and index math for lookups are unchanged. Constants: JSON_ROUND_MILES_DECIMAL_PLACES, JSON_ROUND_HOURS_DECIMAL_PLACES. Implementation: app/results/json_rounding.py, get_scenario_concentration_tolerance in app/results/service.py.

3. Blob layout​

Two containers: rsms-results (or your configured RSMS_RESULTS_BLOB_CONTAINER) for scenario run outputs, and riverbasin (or RSMS_RIVERBASIN_BLOB_CONTAINER) for per-basin configuration (river mile index CSV — §3.2).

3.0 Container rsms-results — scenario results​

Prefix:

{riverbasin_id}/{scenario_id}/
BlobRole
edge_timeseries.jsonPrecomputed Sudhir / chart 3 edge curves (leading, trailing, peak, peak concentration) — schema as implemented (e.g. x_axis: miles, schema_version).
mass_balance.jsonPMASS / DZMASS (and derived totals) vs hours for chart 4 — same information as legacy MassBalance series.

Minification: Production writes must omit pretty-printing to save space and bandwidth.

3.1 Mass balance: blob vs table?​

QuestionAnswer
Used for anything besides the graph?Primary consumer is the mass balance chart (chart 4). Scenario spill quantity and metadata continue to come from scenario / Table Storage entities, not this file.
Small enough?Yes. Typical payload is O(timesteps) × a few floats — almost always far below 64 KiB as a single JSON blob. Recommendation: keep mass_balance.json in blob storage alongside edge_timeseries.json for simplicity and a single “download all artifacts” story. Moving it into Azure Table Storage is optional if we later want all chart payloads in tables for operational uniformity — not required for the 64 KiB limit.

3.2 River mile index (tabular) — CSV in blob (riverbasin container)​

Decision: The tabular river-mile index used for NFQ / closest-station workflows (legacy SQL Server RivermileIndex) is stored as CSV in Blob Storage. It is moderate size, columnar, and easy to inspect and version. This is not the engine’s binary .rmi file (that remains a separate artifact for Neutshell); naming in code should avoid conflating “RMI file” vs “river mile index table.”

ItemValue
Blob containerriverbasin (dedicated container for basin-level config — not the same as rsms-results / scenario outputs).
Blob path{riverbasin_id}/RiverMileIndex.csv (virtual directory = riverbasin_id; filename RiverMileIndex.csv). riverbasin_id is R-YYMMDD-xxx or a legacy UUID (see Azure Table / results keys in rsms-results-table-storage-rework-plan.md).
HeadersSame column names as SQL [RivermileIndex] (e.g. ID, MRM, TRM, UDIntCode, River, Reach, NearestRiverStation — match your exported query).
EncodingUTF-8, header row.

Workers load this file for runs (no pyodbc at runtime). App setting RSMS_RIVERBASIN_BLOB_CONTAINER (example: riverbasin) selects the container; same storage account as other RSMS blobs unless you configure otherwise.

Local dev may use a file path env override pointing at the same CSV layout and column names.

4. Raw engine outputs (engine-files)​

Unchanged in intent: .PLT, .MAS, .INF, .TIM, etc. under engine-files/{riverbasin_id}/{scenario_id}/... for audit and replay. The aggregation pipeline reads local worker paths after the engine finishes; it does not download from blob to rebuild tables.

5. Pipeline (shared package; worker in production)​

Development tooling may call write_scenario_results_to_azure from tests or run_rsms_cli.py; production always runs rsms-worker-function after engine subprocess success (see rsms-results-table-storage-rework-plan.md).

  1. Parse .TIM / .PLT / .MAS (existing parsers).
  2. Resample or bin concentrations onto the 0.2 mi mile grid and hourly time grid agreed with SSOT (M, T).
  3. Write PlumeByTime and PlumeByMile rows with float32 payloads (and metadata entities as needed).
  4. Emit edge_timeseries.json and mass_balance.json (minified) to rsms-results.
  5. Do not write cxplt_time.parquet, cxplt_distance.parquet, or ctplt_*.parquet for the product path once migration is complete.

Legacy CTPLT-heavy charts (2 & 5) may consume the float32 grid from Table reads — open product follow-ups tracked in rsms-master-tasklist.md (deferred in ssot/rsms-ssot.json summarizes chart-level status).

6. Efficient reads (API)​

The FastAPI layer uses the Azure Data Tables client (or REST) for single-row (or batched) reads against the dense grid. Decode bytes → numpy.frombuffer(..., dtype=float32), then shape to (M,) or (T,) as documented in SSOT.

Blob responses for edge_timeseries and mass_balance remain GET blob → parse JSON.

7. Legacy SQL reference (rsms-legacy)​

Still valid for semantic parity: CXPLTResults, CTPLTResults, MassBalance table shapes in ReadOnlySimulationResultsRepository. Physical storage no longer mirrors CXPLTResults as Parquet files.

8. Open items​

  1. Exact table names, partition/row keys, and property naming — align with SSOT + implemented writers (Sudhir *_v2.ipynb reference only).
  2. Sliders / domain endpoints: derive hours_min/max, miles_max from metadata entities or from M, T, mile_step without scanning the whole grid.
  3. CTPLT algorithm and legacy charts 2 and 5 — product backlog (rsms-master-tasklist.md + SSOT deferred).

9. References​

AreaLocation
Rework planrsms-results-table-storage-rework-plan
API readsrsms-api-fastapi/app/results/, OpenAPI /docs; historical snapshot: rsms-api-results-duckdb-design (legacy DuckDB-era filename)
Open backlogrsms-master-tasklist
Sudhir prototypersms-notebooks/river_plume_square_pulse_notebook_block_format/*_v2.ipynb
Legacy edge SQLrsms-legacy / ReadOnlySimulationResultsRepository

10. Archaeology — Parquet + DuckDB (do not revive)​

Historical Parquet blobs + DuckDB read patterns are archived under archive. §§1–6 here are authoritative for the RSMS Suite product.

RSMS results: Table Storage for float32 plume grids; rsms-results JSON blobs for edge timeseries and mass balance; 0.2 mi sample spacing.

Last updated on by jacobgqc